package cn.jussi.dao;

import java.lang.reflect.InvocationTargetException;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.beanutils.BeanUtils;

import cn.jussi.mydbutils.MyDBUtils;

public class BaseDao {
	/**
	 * 占位符替换方法
	 * @param stat
	 * @param args
	 * @return
	 * @throws SQLException
	 */
	private void insteadHolder(PreparedStatement stat, Object... args) throws SQLException {
		for(int i=0; i<args.length; i++) {
			stat.setObject(i+1, args[i]);
		}
		//return stat;
	}
	
	
	/**
	 * 实体获取方法
	 * @param rs
	 * @param clazz
	 * @return
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 * @throws IllegalArgumentException
	 * @throws InvocationTargetException
	 * @throws NoSuchMethodException
	 * @throws SecurityException
	 * @throws SQLException
	 */
	private <T> T getEntity(ResultSet rs, Class<T> clazz) throws InstantiationException, IllegalAccessException, IllegalArgumentException, InvocationTargetException, NoSuchMethodException, SecurityException, SQLException {	
		T entity = (T) clazz.getDeclaredConstructor().newInstance();	
		ResultSetMetaData rsmd = rs.getMetaData();		//获取源数据
		int columnCount = rsmd.getColumnCount();	//获取要查询的列的总列数		
		for(int i=0; i<columnCount; i++){
			String key = rsmd.getColumnLabel(i+1);		//获取该列的列名
			Object val = rs.getObject(key);		//根据列名获取到数据
			/*Field field = clazz.getDeclaredField(key);
			field.setAccessible(true);
			field.set(entity, val);*/
			BeanUtils.setProperty(entity, key, val);	//beanutils包 依赖 logging 包，导入时要导两个包
		}	
		return entity;
	}
	
	
	/**
	 * 通用的 增、删、改 方法
	 * @param sql
	 * @param args
	 * @return
	 */
	public int IUD (String sql, Object... args) {
		int count=0;
		Connection conn = null;
		PreparedStatement stat = null;	
		try {
			conn = MyDBUtils.getConnection();
			stat = conn.prepareStatement(sql);
			insteadHolder(stat, args);
			count = stat.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			MyDBUtils.close(conn, stat, null);
		}
		return count;	
	}
	
	
	/**
	 * 适合事务处理的 IUD 方法
	 * @param conn
	 * @param sql
	 * @param args
	 * @return
	 */
	public int IUD (Connection conn, String sql, Object... args) {
		int count=0;
		PreparedStatement stat = null;	
		try {
			stat = conn.prepareStatement(sql);
			insteadHolder(stat, args);
			count = stat.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			MyDBUtils.close(null, stat, null);
		}
		return count;	
	}
	
	/**
	 * 能获取自动生成的那个主键 值的 插入方法
	 * @param sql
	 * @param args
	 * @return
	 */
	public int insertReturnAutoColumn(String sql, Object... args) {
		int id = 0;
		Connection conn = null;
		PreparedStatement stat = null;
		ResultSet rs = null;
		try {
			conn = MyDBUtils.getConnection();
			stat = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			insteadHolder(stat, args);
			stat.executeUpdate();
			rs = stat.getGeneratedKeys();	
			if(rs.next()) {
				id = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			MyDBUtils.close(conn, stat, null);
		}
		return id;		
	}
	
	/**
	 * 通用的 一条记录 获取 方法
	 * @param clazz
	 * @param sql
	 * @param args
	 * @return
	 */
	public <T> T getOneData(Class<T> clazz, String sql, Object... args) {
		Connection conn = null;
		PreparedStatement stat = null;
		ResultSet rs = null;
		T entity = null;	
		try {
			conn = MyDBUtils.getConnection();
			stat = conn.prepareStatement(sql);
			insteadHolder(stat, args);
			rs = stat.executeQuery();
			if(rs.next()) {
				entity = (T) getEntity(rs, clazz);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			MyDBUtils.close(conn, stat, rs);
		}	
		return entity;
	}
	
	
	/**
	 * 通用的 多条记录 获取 方法
	 * @param clazz
	 * @param sql
	 * @param args
	 * @return
	 */
	public <T> List<T> getListData(Class<T> clazz, String sql, Object... args){
		Connection conn = null;
		PreparedStatement stat = null;
		ResultSet rs = null;
		T entity = null;
		List<T> list = new ArrayList<>();	
		try {
			conn = MyDBUtils.getConnection();
			stat = conn.prepareStatement(sql);
			insteadHolder(stat, args);
			rs = stat.executeQuery();	
			while(rs.next()) {
				entity = (T) getEntity(rs, clazz);
				list.add(entity);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			MyDBUtils.close(conn, stat, rs);
		}		
		return list;
	}
	
	
	/**
	 * 获取满足查询条件的某个字段或者某个统计字段（ count（*））
	 * @param sql
	 * @param args
	 * @return
	 */
	public Object getOneColumn(String sql, Object... args) {
		Connection conn = null;
		PreparedStatement stat = null;
		ResultSet rs = null;	
		try {
			conn = MyDBUtils.getConnection();
			stat = conn.prepareStatement(sql);
			insteadHolder(stat, args);
			rs = stat.executeQuery();	
			if(rs.next()) {
				return rs.getObject(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			MyDBUtils.close(conn, stat, rs);
		}
		return null;
	}
	
	/**
	 * 专门的 Blob 列获取方法
	 * @param sql
	 * @param args
	 * @return
	 */
	public Blob getOneColumnBlob(String sql, Object... args) {
		Connection conn = null;
		PreparedStatement stat = null;
		ResultSet rs = null;	
		try {
			conn = MyDBUtils.getConnection();
			stat = conn.prepareStatement(sql);
			insteadHolder(stat, args);
			rs = stat.executeQuery();	
			if(rs.next()) {
				return rs.getBlob(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			MyDBUtils.close(conn, stat, rs);
		}
		return null;
	}
}
